﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace NG
{
    class RolesHandler:IRolesHandler
    {
        private string tableName = "[dbo].[Roles]";
        private string tableName1 = "[dbo].[ProjectDepartmentRoles]";
      
        public void addRole(Roles role)
        {
            DBConnect con = new DBConnect();
            string tbn = "[" + con.dbName + "]." + this.tableName;
            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }

            string query = string.Format("INSERT INTO " + tbn + " (roleName,roleDesc,roleAddress,intervieweeName) VALUES ('{0}','{1}','{2}','{3}') ; SELECT roleNo FROM " +tbn+ " WHERE roleNo = @@IDENTITY;",
                role.roleName,role.roleDesc,role.roleAddress,role.intervieweeName);

            try
            {
                SqlCommand cmd = new SqlCommand(query, con.connection);
                role.roleNum = int.Parse(cmd.ExecuteScalar().ToString());
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't disconnect from the Database");
            }

        }

        public void deleteRole(Roles role)
        {
            DBConnect con = new DBConnect();
            string tbn = "[" + con.dbName + "]." + this.tableName;

            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }

            string query = "DELETE FROM " + tbn + " WHERE roleNo = " + role.roleNum;
            SqlCommand cmd = new SqlCommand(query, con.connection);
            cmd.ExecuteNonQuery();

            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't disconnect from the Database");
            }
        }

        public void updateRole(Roles role, Roles newrole)
        {
            DBConnect con = new DBConnect();
            string tbn = "[" + con.dbName + "]." + this.tableName;
            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }
            string query = "UPDATE" + tbn + " SET " +
               " roleName='" + newrole.roleName + "', roleDesc='" + newrole.roleDesc + "'"
                + "WHERE roleNo=" + role.roleNum;
            SqlCommand cmd = new SqlCommand(query, con.connection);
            cmd.ExecuteNonQuery();
            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't disconnect from the Database");
            }
        }

        public LinkedList<Roles> allRoles(string sortedBy, bool desc)
        {
            string sorted;
            if (sortedBy == string.Empty)
            {
                sorted = " {0}";
            }
            else
            {
                sorted = sortedBy;
            }
            DBConnect con = new DBConnect();
            string tbn = "[" + con.dbName + "]." + this.tableName;
            LinkedList<Roles> rolesList = new LinkedList<Roles>();
            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }
            string extra = "";
            if (desc)
            {
                extra = " DESC";
            }

            string query = "SELECT * FROM " + tbn + "ORDER BY " + sorted + extra;
            SqlCommand cmd = new SqlCommand(query, con.connection);
            SqlDataReader rdr = cmd.ExecuteReader();


            if (rdr.HasRows)
            {

                while (rdr.Read())
                {

                    Roles role = new Roles(int.Parse(rdr["roleNo"].ToString()),
                            rdr["roleName"].ToString(),rdr["roleDesc"].ToString(), rdr["roleAddress"].ToString(),rdr["intervieweeName"].ToString());


                    rolesList.AddLast(role);


                }
            }
            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't disconnect from the Database");
            }
            return rolesList;
        }


        public LinkedList<Roles> allRolesGivenProject(string sortedBy, bool desc, Project pro)
        {
            string sorted;
            if (sortedBy == string.Empty)
            {
                sorted = " {0}";
            }
            else
            {
                sorted = sortedBy;
            }
            DBConnect con = new DBConnect();
            string tbn = "[" + con.dbName + "]." + this.tableName;
            string tbn1 = "[" + con.dbName + "]." + this.tableName1;
            LinkedList<Roles> rolesList = new LinkedList<Roles>();
            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }
            string extra = "";
            if (desc)
            {
                extra = " DESC";
            }

            string query = string.Format("SELECT * FROM " + tbn,tbn1 + "WHERE role = roleNO AND project ={0} ORDER BY " 
                + sorted + extra ,pro.ID);
            SqlCommand cmd = new SqlCommand(query, con.connection);
            SqlDataReader rdr = cmd.ExecuteReader();


            if (rdr.HasRows)
            {

                while (rdr.Read())
                {

                    Roles role = new Roles(int.Parse(rdr["roleNo"].ToString()),
                            rdr["roleName"].ToString(), rdr["roleDesc"].ToString(), rdr["roleAddress"].ToString(), rdr["intervieweeName"].ToString());


                    rolesList.AddLast(role);


                }
            }
            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't disconnect from the Database");
            }
            return rolesList;
        }
    }
}
